clear
set mem 300m
capture log close
log using "$dataloc/Log/ifs_data", text replace


/* --------------------------------------------------------------- *
    Author: Ed Al-Hussainy
    Description:  Process raw IFS data for Financial Structure Database
 * --------------------------------------------------------------- */
 
forvalues i = 1/10 { 
    tempfile tmp`i' 
}

/* --------------------------------------------------------------- *
    Read-in raw data (source: DDP)
 * --------------------------------------------------------------- */
 
* USE: readmerger A B C D
* Program reads in all files with extension A in directory B, merges them using variable(s) C and saves a single output file D

capture program drop readmerger
program readmerger  
    args filext dir mergevars outds 

    quietly {

        local list : dir "`dir'" files "`filext'" , nofail
        local listn : word count `list'
        global ifsvars ""

        cd "`dir'"  

        forvalues i = 1/`listn' {

            local fil : word `i' of `list'
            local ser = subinstr("`fil'",".txt","",.)
            global bsvars = trim("$bsvars data`ser'")

            noisily di "Currently processing `fil' (file `i' of `listn')"

            insheet using "`fil'", names clear           
                       
            isid `mergevars'
              
            if `i' == 1 {
                save `outds', replace
            }
            else if `listn' > 1 {
                merge `mergevars' using `outds', sort
                noisily tab _merge
                keep if _merge == 3
                drop _merge* 
                save `outds', replace
            }
        } 
    }    
end

readmerger *.txt "$dataloc/Data/IFS/$dataver" "countrycode timename" IFSRaw

drop countryname timecode
rename countrycode cncode
rename timename year

ds
local allvars "`r(varlist)'"
local idvars "cncode year"
local datavars : list allvars - idvars 

foreach i in `datavars' {
   local _lab0 = "`:var label `i''"
   local _lab1 = substr("`_lab0'",1,7)
   local _lab2 = subinstr("`_lab1'",".","_",.)
   local _l = -1 * (length("`lab0'") - 19)
   local _lab3 = substr("`_lab0'",`_l',.)
   label var `i' "`_lab3'"
   rename `i' line`_lab2'
}  

reshape long line, i(cncode year) j(series_code) string

rename line value
* treat value == 0 as missing
replace value = "" if value == ".."
destring value, replace
replace value = . if value == 0
drop if missing(value)

replace series_code = subinstr(series_code,"_",".",.)

isid cncode year series_code

sort cncode
save "$dataloc/Data/IFS/$dataver/IFSMain", replace

capture erase "$dataloc/Data/IFS/$dataver/IFSRaw.dta"

/* --------------------------------------------------------------- *
    EURO adjustment
    Fixed euro conversion rates from http://www.ecb.de/
 * --------------------------------------------------------------- */


use "$dataloc/Data/IFS/$dataver/IFSMain", clear

clear
input str3 cncode xrate1euro eurozone eurostartyear
    AUT 13.7603 1 1999
    BEL 40.3399 1 1999
    FIN 5.94573 1 1999
    FRA 6.55957 1 1999
    DEU 1.95583 1 1999
    GRC 340.750 1 2001
    IRL 0.787564 1 1999
    ITA 1936.27 1 1999
    LUX 40.3399 1 1999
    NLD 2.20371 1 1999
    PRT 200.482 1 1999
    ESP 166.386 1 1999
    SMR 1936.27 1 1999
    SVN 239.640 1 2007
    CYP 0.585274 1 2008
    MLT 0.4293 1 2008
    SVK 30.1260 1 2009
end

sort cncode

merge cncode using "$dataloc/Data/IFS/$dataver/IFSMain"
tab _merge
drop _merge

* for Eurosystem, use "of-which" lines that show positions with residents of the country 
* (as opposed to the Eurosystem area) for data consistency with pre-1999 data
drop if eurozone == 1 & (substr(series_code,5,1) == "U")


/* --------------------------------------------------------------- *
   select GDP series
 * --------------------------------------------------------------- */

drop if substr(series_code,1,3) == "99B" & !inlist(series_code,"99B..ZF","99B..ZW", "99B.CZF","99B.CZW", "99B.DZF")

/* --------------------------------------------------------------- *
    Position 10
 * --------------------------------------------------------------- */
 
* for each series, retain pos. 10 = F (source=IFS) if available
gen series_code6 = substr(series_code,1,6)
gen pos10 = substr(series_code,7,1)
replace pos10 = "_" if pos10 =="."
tab pos10

keep cncode year value series_code6 pos10 eurozone eurostartyear
reshape wide value, i(cncode year series_code6 eurozone eurostartyear) j(pos10) string

gen value = .

replace value = valueF 
replace value = valueK if missing(value)

* eurozone adjustment = series in euros after euro adoption
replace value = valueW if eurozone == 1 & year >= eurostartyear & !missing(valueW)

/* --------------------------------------------------------------- *
    Position 9
 * --------------------------------------------------------------- */

gen series_code5 = substr(series_code6,1,5)
gen pos9 = substr(series_code6,6,1)
replace pos9 = "_" if pos9 =="."
tab pos9

keep cncode year value series_code5 pos9
reshape wide value, i(cncode year series_code5) j(pos9) string


/* --------------------------------------------------------------- *
    QUICK FIX: El Salvador's GDP series were reported in LCU until
   2006. By dropping this LCU line, we default to the dollar series 
    with code 99B.D.
 * --------------------------------------------------------------- */

drop if series_code5 == "99B.." & cncode=="SLV"

gen value = .
replace value = valueZ 
drop if missing(value)


/* --------------------------------------------------------------- *
    Position 8
 * --------------------------------------------------------------- */

* for each series, retain pos. 8 = . (loc, unadjusted data) if available
gen series_code4 = substr(series_code5,1,4)
gen pos8 = substr(series_code5,5,1)
replace pos8 = "_" if pos8 =="."
tab pos8

keep cncode year value series_code4 pos8
reshape wide value, i(cncode year series_code4) j(pos8) string

capture drop valueA 
capture drop valueX

gen value = .
* national currency (n.c.) data or unadjusted data
replace value = value_ 
* seasonally adjusted by IMF
replace value = valueB if missing(value)
* seasonally adjusted by reporting country
replace value = valueC if missing(value)
* US $
replace value = valueD if missing(value)

drop value_ valueB valueC valueD

* no. of subseries with nonmissing values
egen _t2 = rownonmiss(valueG - valueS) if missing(value)
egen _t3 = rowtotal(valueG - valueS) if _t2 == 1
replace value = _t3 if _t2 == 1

* di "Check these series for double counting (see IFS Code position 8):"
* gen _t4 = cncode + "_" + series_code4
* tab _t4 if _t2 > 1 & !missing(_t2)

* KOR - Postal Banks 
replace value = valueH if _t2 == 2 & series_code4 == "45.." & cncode == "KOR"

* NOR - Claims on Cent. Gov
replace value = valueL if _t2 == 2 & series_code4 == "42A." & cncode == "NOR"

* NZL - Claims on Cent. Gov
replace value = valueS if _t2 == 2 & series_code4 == "42A." & cncode == "NZL"
replace value = valueS if _t2 == 2 & series_code4 == "42D." & cncode == "NZL"

drop if missing(value)

isid cncode year series_code4
keep cncode year series_code4 value    

/* --------------------------------------------------------------- *
    Position 7
 * --------------------------------------------------------------- */

gen series_code3 = substr(series_code4,1,3)
gen pos7 = substr(series_code4,4,1)
replace pos7 = "_" if pos7 =="."
tab pos7

keep cncode year value series_code3 pos7
reshape wide value, i(cncode year series_code3) j(pos7) string

gen value = .
replace value = value_

* no. of subseries with nonmissing values
egen _t2 = rownonmiss(valueA - valueX) if missing(value)
egen _t3 = rowtotal(valueA - valueX) if _t2 == 1
replace value = _t3 if _t2 == 1

* CHN - Foreign Currency Deposits 
replace value = valueB if _t2 == 2 & series_code3 == "25A" & cncode == "CHN"

* LKA - Claims on nonfinancial public enterprises + claims on coops
replace value = _t3 if _t2 == 2 & series_code3 == "22C" & cncode == "LKA"

drop if missing(value)

isid cncode year series_code3
keep cncode year series_code3 value   

replace  series_code3 = subinstr(series_code3,".","_",.)

reshape wide value, i(cncode year) j(series_code3) string

sort cncode year

isid cncode year

compress
save "$dataloc/Data/IFS/$dataver/IFS", replace


/* end of code */
clear
log close

